﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace WebApplication10.App_Code
{
    [DataObject]
    public class JobCategory
    {
        public int JobCategoryID { get; set; }
        public string Name { get; set; }
        public int CompanyID { get; set; }

        [DataObjectMethod(DataObjectMethodType.Select, true)]
        public static List<JobCategory> GetJobCategoriesByName(string name) // Select.
        {
            List<JobCategory> results = new List<JobCategory>();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from JobCategory where name = @name";
                comm.Parameters.AddWithValue("@name", name);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    JobCategory jc = new JobCategory();
                    jc.JobCategoryID = (int)dr["JobCategoryID"];
                    jc.Name = (string)dr["Name"];
                    jc.CompanyID = (int)dr["CompanyID"];
                    results.Add(jc);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return results;
        }

        public static List<JobCategory> GetJobCategories() // Select.
        {
            List<JobCategory> results = new List<JobCategory>();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from JobCategory";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    JobCategory jc = new JobCategory();
                    jc.JobCategoryID = (int)dr["JobCategoryID"];
                    jc.Name = (string)dr["Name"];
                    jc.CompanyID = (int)dr["CompanyID"];
                    results.Add(jc);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return results;
        }

        public static void InsertJobCate(int JobCategoryID, string Name, int CompanyID)
        {
            SqlConnection conn = null;

            try
            {
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn = new SqlConnection();
                conn.Open();
                conn.ConnectionString = connstr;
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Insert into Fee (JobCategoryID, Name, CompanyID) VALUE (@JobCategoryID, @Name, @CompanyID)";
                comm.Parameters.AddWithValue("@JobCategoryID", JobCategoryID);
                comm.Parameters.AddWithValue("@Name", Name);
                comm.Parameters.AddWithValue("@CompanyID", CompanyID);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        }
    }


